# Copyright (c) 2020 Presto Labs Pte. Ltd.
# Author: jhkim

import pandas
from sqlalchemy import create_engine


def _gen_engine():
  engine = create_engine(
      "mysql+mysqldb://feed_stat_querier:#dXfc%DGtXg%SfBX@coin-db.corp.prestolabs.io"
      "/coin2_feed_stats_20200228",
      echo=False)
  return engine


"""
Asset	Current Face Value	Adjusted Face Value	Adjustment Multiplier
BTC	0.0001 BTC	0.01 BTC	100
EOS	0.1 EOS	10 EOS	100
ETH	0.001 ETH	0.1 ETH	100
LTC	0.001 LTC	1 LTC	1000
BCH	0.001 BCH	0.1 BCH	100
XRP	1 XRP	100 XRP	100
ETC	0.1 ETC	10 ETC	100
BSV	0.001 BSV	1 BSV	1000
TRX	1 TRX	1,000 TRX	1000
"""


def generate_casewhen_usdt_dict(usdt_dict):
  whens = [
      f"when tbl.symbol like \"{key}\" then tbl.vwap*{value}" for key, value in usdt_dict.items()
  ]
  whenstr = "\n".join(whens)
  return f"case {whenstr} else 0 end"


OKEX_HBDM_MULTIP_FUT = "(case when tbl.symbol like 'btc%' then 100 else 10 end)"
OKEX_USDT_FUT_UNTIL_20200327 = {
    "btc%": 0.0001,
    "eos%": 0.1,
    "eth%": 0.001,
    "ltc%": 0.001,
    "bch%": 0.001,
    "xrp%": 1,
    "etc%": 0.1,
    "bsv%": 0.001,
    "trx%": 1,
}
OKEX_USDT_FUT_SINCE_20200328 = {
    "btc%": 0.01,
    "eos%": 10,
    "eth%": 0.1,
    "ltc%": 1,
    "bch%": 0.1,
    "xrp%": 100,
    "etc%": 10,
    "bsv%": 1,
    "trx%": 1000,
}
OKEX_USDT_MULTIP_FUT = f"""
(
case
when tbl.trading_date <= 20200327 then
({generate_casewhen_usdt_dict(OKEX_USDT_FUT_UNTIL_20200327)})
else
({generate_casewhen_usdt_dict(OKEX_USDT_FUT_SINCE_20200328)})
end
)
"""


def generate_schedules():
  futures = [("okex_usdt_futures", "Futures", "Okex", OKEX_USDT_MULTIP_FUT, '%-USDT.%'),
             ("okex_usd_futures", "Futures", "Okex", OKEX_HBDM_MULTIP_FUT, '%-USD.%'),
             ("huobi_usd_futures", "Futures", "Huobi", OKEX_HBDM_MULTIP_FUT, '%-USD.%'),
             ("bitmex_usd_futures", "Futures", "Bitmex", 1, '%-USD.%'),
             ("binance_usdt_futures", "Futures", "Binance", "tbl.vwap", '%-USDT.%')]
  stablecoins_spots = [
      (f"{exchange.lower()}_{quote.lower()}_spot", "Spot", exchange, "tbl.vwap", f'%-{quote}')
      for quote in ["USDT", "USDC", "BUSD", "HUSD", "TUSD"]
      for exchange in ["Binance", "Huobi", "Okex"]
  ]
  quote_to_usd = {
      "USD": 1,
      "EUR": 1.08,
      "GBP": 1.23,
      "JPY": 0.0093,
      "KRW": 0.00082,
  }
  fiats_spots = [(f"{exchange.lower()}_{quote.lower()}_spot",
                  "Spot",
                  exchange,
                  f"tbl.vwap * {quote_to_usd[quote]}",
                  f'%-{quote}')
                 for quote in ["USD", "EUR", "GBP", "JPY", "KRW"]
                 for exchange in ["Gdax", "Kraken", "Quoinex", "Upbit", "Coinone", "Bithumb"]]
  bitflyers = [("bitflyer_jpy_swap",
                "Futures",
                "Bitflyer",
                f"tbl.vwap * {quote_to_usd['JPY']}",
                '%PERPETUAL'),
               ("bitflyer_jpy_spot",
                "Futures",
                "Bitflyer",
                f"tbl.vwap * {quote_to_usd['JPY']}",
                '%IMMEDIATE')]
  return futures + stablecoins_spots + fiats_spots + bitflyers


def generate_sqls_volumetrend(trading_date_from, trading_date_to, symbolgroup=False):
  if symbolgroup:
    selcol = "SUBSTRING_INDEX(tbl.symbol, '.', 1) as symbolgroup"
    grpcol = "symbolgroup"
  else:
    selcol = "tbl.symbol"
    grpcol = "symbol"

  for title, market_type, exchange, multiplier, patt in generate_schedules():
    yield title, [
        f"""
SELECT
tbl.trading_date, tbl.exchange, tbl.market_type,
{selcol},
sum((tbl.buy_volume + tbl.sell_volume) * {multiplier}) as total_notional
FROM coin2_feed_stats_20200228.StatsView as tbl
where trading_date>="{trading_date_from}" and trading_date<="{trading_date_to}"
and tbl.symbol like "{patt}"
and tbl.machine="feed-05.ap-northeast-1.aws"
and tbl.market_type="{market_type}"
and tbl.exchange="{exchange}"
group by exchange, market_type, {grpcol}, trading_date
order by trading_date, exchange, market_type, {grpcol}
        """, f"""
SELECT
tbl.trading_date, tbl.exchange, tbl.market_type,
{selcol},
sum((tbl.buy_volume + tbl.sell_volume) * {multiplier}) as total_notional
FROM coin_feed_stat_20180514.StatsView as tbl
where trading_date>="{trading_date_from}" and trading_date<="{min(trading_date_to, 20200330)}"
and tbl.symbol like "{patt}"
and tbl.machine >= "feed-02.ap-northeast-1.aws" and tbl.machine <= "feed-04.ap-northeast-1.aws"
and tbl.market_type="{market_type}"
and tbl.exchange="{exchange}"
group by exchange, market_type, {grpcol}, trading_date
order by trading_date, exchange, market_type, {grpcol}
        """, f"""
SELECT
tbl.trading_date, tbl.exchange, tbl.market_type,
{selcol},
sum((tbl.buy_volume + tbl.sell_volume) * {multiplier}) as total_notional
FROM coin_feed_stat_20180514.StatsView as tbl
where trading_date>="{trading_date_from}" and trading_date<="{min(trading_date_to, 20200110)}"
and tbl.symbol like "{patt}"
and tbl.machine = "feed-01.ap-northeast-1.aws"
and tbl.market_type="{market_type}"
and tbl.exchange="{exchange}"
group by exchange, market_type, {grpcol}, trading_date
order by trading_date, exchange, market_type, {grpcol}
        """
    ]


def generate_sqls_leadingdigit(trading_date, pattern):
  for title, market_type, exchange, multiplier, patt in generate_schedules():
    if exchange == "Gdax":
      machine = "feed-02.us-east-1.aws"
    else:
      machine = "feed-01.ap-northeast-1.aws.huobi"
    yield title, [
        f"""
SELECT tbl.*
FROM feed_stat_20200803.StatsView as tbl
where trading_date="{trading_date}"
and tbl.symbol like "{pattern}"
and tbl.symbol like "{patt}"
and tbl.market_type="{market_type}"
and tbl.exchange="{exchange}"
and tbl.total_volume > 0
and tbl.machine="{machine}"
group by exchange, market_type, symbol, trading_date
order by trading_date, exchange, market_type, symbol"""]


def enumerate_volume_trend(trading_date_from, trading_date_to, symbolgroup=False):
  engine = _gen_engine()
  for nametuple, sqls in generate_sqls_volumetrend(int(trading_date_from), int(trading_date_to),
                                                   symbolgroup):
    dfs = []
    for sql in sqls:
      sql = sql.replace("\n", " ").replace("%", "%%").replace("  ", " ").replace("  ", " ")
      print(sql)
      df = pandas.read_sql(sql, engine)
      if len(df) > 0:
        dfs.append(df)
    if len(dfs) > 0:
      df = pandas.concat(dfs, axis=0).sort_values(['trading_date']).reset_index(drop=True)
      yield nametuple, df


def enumerate_leading_digit(trading_date, pattern):
  engine = _gen_engine()
  for nametuple, sqls in generate_sqls_leadingdigit(int(trading_date), pattern):
    dfs = []
    for sql in sqls:
      sql = sql.replace("\n", " ").replace("%", "%%").replace("  ", " ").replace("  ", " ")
      df = pandas.read_sql(sql, engine)
      if len(df) > 0:
        dfs.append(df)
    if len(dfs) > 0:
      df = pandas.concat(dfs, axis=0).sort_values(['trading_date']).reset_index(drop=True)
      yield nametuple, df
